﻿using SlamDunk.CMS.Model.DataModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Text;
using SD.Common.PoweredByLee;

namespace SlamDunk.CMS.DAL
{
    public class ScoreLineDal
    {
        #region # 静态构造器

        /// <summary>
        /// SqlHelper
        /// </summary>
        private static readonly SqlHelper _SqlHelper;

        /// <summary>
        /// 静态构造器
        /// </summary>
        static ScoreLineDal()
        {
            _SqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
        }

        #endregion

        #region 01.根据年份与省份条件获取记录条数
        /// <summary>
        /// 根据年份与省份条件获取记录条数
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="prov">省份</param>
        /// <returns>记录条数</returns>
        public int GetCount(short year, string prov)
        {
            string sql = "SELECT COUNT(*) FROM dbo.ScoreLines WHERE 0 = 0";

            #region 非空校验
            if (year != 0)
            {
                sql = string.Format("{0} AND [Year] = {1}", sql, year);
            }
            if (!string.IsNullOrWhiteSpace(prov))
            {
                sql = string.Format("{0} AND Province LIKE '%{1}%'", sql, prov.FilterSql());
            }
            #endregion

            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 02.根据年份与省份条件获取获取实体对象集合
        /// <summary>
        /// 根据年份与省份条件获取获取实体对象集合
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="year">年份</param>
        /// <param name="prov">省份</param>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList(int start, int end, short year, string prov)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY [Year] DESC, Province ASC, Major ASC) AS RowIndex FROM dbo.ScoreLines WHERE 0 = 0";

            #region 非空校验
            if (year != 0)
            {
                sql = string.Format("{0} AND [Year] = {1}", sql, year);
            }
            if (!string.IsNullOrWhiteSpace(prov))
            {
                sql = string.Format("{0} AND [Province] LIKE '%{1}%'", sql, prov.FilterSql());
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 03.获取所有年份
        /// <summary>
        /// 获取所有年份
        /// </summary>
        /// <returns>年份集合</returns>
        public List<short> GetAllYear()
        {
            List<short> list = new List<short>();

            string sql = "SELECT DISTINCT [Year] FROM dbo.ScoreLines ORDER BY [Year] DESC";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add((short)(reader as SqlDataReader)[0]);
                }
            }
            return list;
        }
        #endregion

        #region 04.获取所有省份
        /// <summary>
        /// 获取所有年份
        /// </summary>
        /// <returns>省份集合</returns>
        public List<string> GetAllProv()
        {
            List<string> list = new List<string>();

            string sql = "SELECT DISTINCT Province FROM dbo.ScoreLines";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add((string)(reader as SqlDataReader)[0]);
                }
            }
            return list;
        }
        #endregion

        #region 00.单例构造器
        /// <summary>
        /// 构造方法
        /// </summary>
        public ScoreLineDal() { }

        /// <summary>
        /// 创建对象静态方法
        /// </summary>
        /// <returns>ScoreLineDal实例</returns>
        public static ScoreLineDal CreateInstance()
        {
            ScoreLineDal scoreLineDal = CallContext.GetData(typeof(ScoreLineDal).Name) as ScoreLineDal;
            if (scoreLineDal == null)
            {
                scoreLineDal = new ScoreLineDal();
                CallContext.SetData(typeof(ScoreLineDal).Name, scoreLineDal);
            }
            return scoreLineDal;
        }
        #endregion

        #region 01.添加方法
        /// <summary>
        /// 添加一个实体对象
        /// </summary>
        /// <param name="scoreLine">要添加的实体对象</param>
        /// <returns>添加完毕后的实体对象</returns>
        public ScoreLine Add(ScoreLine scoreLine)
        {
            string sql = "INSERT INTO ScoreLines (Province, Year, Major, Category, TopScore, BottomScore, AvgScore, AdmitCount, DelFlag, AddTime)  output inserted.Id VALUES (@Province, @Year, @Major, @Category, @TopScore, @BottomScore, @AvgScore, @AdmitCount, @DelFlag, @AddTime)";
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Province", ToDBValue(scoreLine.Province)),
                new SqlParameter("@Year", ToDBValue(scoreLine.Year)),
                new SqlParameter("@Major", ToDBValue(scoreLine.Major)),
                new SqlParameter("@Category", ToDBValue(scoreLine.Category)),
                new SqlParameter("@TopScore", ToDBValue(scoreLine.TopScore)),
                new SqlParameter("@BottomScore", ToDBValue(scoreLine.BottomScore)),
                new SqlParameter("@AvgScore", ToDBValue(scoreLine.AvgScore)),
                new SqlParameter("@AdmitCount", ToDBValue(scoreLine.AdmitCount)),
                new SqlParameter("@DelFlag", ToDBValue(scoreLine.DelFlag)),
                new SqlParameter("@AddTime", ToDBValue(scoreLine.AddTime)),
            };
            //根据newId返回新添加的实体对象
            int newId = (int)_SqlHelper.ExecuteScalar(sql, args);
            return this.GetModel(newId);
        }
        #endregion

        #region 02.删除方法（物理删除）
        /// <summary>
        /// 删除一个实体对象
        /// </summary>
        /// <param name="id">要删除的实体对象Id</param>
        /// <returns>受影响的行数</returns>
        public int PhysicalDelete(int id)
        {
            string sql = "DELETE FROM ScoreLines WHERE Id = @Id";
            SqlParameter arg = new SqlParameter("@Id", id);
            return _SqlHelper.ExecuteNonQuery(sql, arg);
        }
        #endregion

        #region 03.删除方法（逻辑删除）
        /// <summary>
        /// 删除一个实体对象
        /// </summary>
        /// <param name="id">要删除的实体对象Id</param>
        /// <returns>受影响的行数</returns>
        public int LogicDelete(int id)
        {
            string sql = "UPDATE ScoreLines SET DelFlag = 0 WHERE Id = @Id";
            SqlParameter arg = new SqlParameter("@Id", id);
            return _SqlHelper.ExecuteNonQuery(sql, arg);
        }
        #endregion

        #region 04.修改方法
        /// <summary>
        /// 修改一个实体对象
        /// </summary>
        /// <param name="scoreLine">构造好的要修改的实体对象</param>
        /// <returns>受影响的行数</returns>
        public int Update(ScoreLine scoreLine)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE ScoreLines SET Province = @Province");
            sql.Append(", Year = @Year");
            sql.Append(", Major = @Major");
            sql.Append(", Category = @Category");
            sql.Append(", TopScore = @TopScore");
            sql.Append(", BottomScore = @BottomScore");
            sql.Append(", AvgScore = @AvgScore");
            sql.Append(", AdmitCount = @AdmitCount");
            sql.Append(", DelFlag = @DelFlag");
            sql.Append(", AddTime = @AddTime");
            sql.Append(" WHERE Id = @Id");
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Id", scoreLine.Id)
                ,new SqlParameter("@Province", ToDBValue(scoreLine.Province))
                ,new SqlParameter("@Year", ToDBValue(scoreLine.Year))
                ,new SqlParameter("@Major", ToDBValue(scoreLine.Major))
                ,new SqlParameter("@Category", ToDBValue(scoreLine.Category))
                ,new SqlParameter("@TopScore", ToDBValue(scoreLine.TopScore))
                ,new SqlParameter("@BottomScore", ToDBValue(scoreLine.BottomScore))
                ,new SqlParameter("@AvgScore", ToDBValue(scoreLine.AvgScore))
                ,new SqlParameter("@AdmitCount", ToDBValue(scoreLine.AdmitCount))
                ,new SqlParameter("@DelFlag", ToDBValue(scoreLine.DelFlag))
                ,new SqlParameter("@AddTime", ToDBValue(scoreLine.AddTime))
            };
            return _SqlHelper.ExecuteNonQuery(sql.ToString(), args);
        }
        #endregion

        #region 05.根据主键获取单个实体对象
        /// <summary>
        /// 根据主键获取单个实体对象
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>单个实体对象</returns>
        public ScoreLine GetModel(int id)
        {
            string sql = "SELECT * FROM ScoreLines WHERE Id = @Id";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 06.获取实体对象集合
        /// <summary>
        /// 获取实体对象集合
        /// </summary>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList()
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = "SELECT * FROM ScoreLines WHERE DelFlag = 0";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 07.获取总记录条数
        /// <summary>
        /// 返回总记录条数
        /// </summary>
        /// <returns>总记录条数</returns>
        public int GetCount()
        {
            string sql = "SELECT COUNT(*) FROM ScoreLines WHERE DelFlag = 0";
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 08.分页获取实体对象集合
        /// <summary>
        /// 分页获取实体对象集合
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <returns>实体集合</returns>
        public List<ScoreLine> GetModelList(int start, int end)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = "SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM ScoreLines WHERE DelFlag = 0) AS t WHERE RowIndex >= @start AND RowIndex <= @end";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 09.根据条件获取记录条数（1个参数）
        /// <summary>
        /// 根据条件获取记录条数（1个参数）
        /// </summary>
        /// <param name="field">字段名称</param>
        /// <param name="arg">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field, string arg)
        {
            string sql = string.Format("SELECT COUNT(*) FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%'", field, arg.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 10.根据条件获取记录条数（2个参数）
        /// <summary>
        /// 根据条件获取记录条数（2个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string filed2, string arg2)
        {
            string sql = string.Format("SELECT COUNT(*) FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%'", field1, arg1.FilterSql(), filed2, arg2.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 11.根据条件获取记录条数（3个参数）
        /// <summary>
        /// 根据条件获取记录条数（3个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string field2, string arg2, string field3, string arg3)
        {
            string sql = string.Format("SELECT COUNT(*) FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%'", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 12.根据条件获取记录条数（4个参数）
        /// <summary>
        /// 根据条件获取记录条数（4个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="field4">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <param name="arg4">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string field2, string arg2, string field3, string arg3, string field4, string arg4)
        {
            string sql = string.Format("SELECT COUNT(*) FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%' AND {6} LIKE '%{7}%'", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql(), field4, arg4.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 13.根据条件分页获取实体对象集合（1个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（1个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field">字段名称</param>
        /// <param name="arg">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList(int start, int end, string field, string arg)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field, arg.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 14.根据条件分页获取实体对象集合（2个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（2个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 15.根据条件分页获取实体对象集合（3个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（3个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2, string field3, string arg3)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 16.根据条件分页获取实体对象集合（4个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（4个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="field4">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <param name="arg4">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<ScoreLine> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2, string field3, string arg3, string field4, string arg4)
        {
            List<ScoreLine> list = new List<ScoreLine>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM ScoreLines WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%' AND {6} LIKE '%{7}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql(), field4, arg4.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 17.根据IDataReader对象返回实体对象方法
        /// <summary>
        /// 根据IDataReader对象返回实体对象方法
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <returns>实体对象</returns>
        public ScoreLine ToModel(IDataReader reader)
        {
            ScoreLine scoreLine = new ScoreLine();
            scoreLine.Id = (int)ToModelValue(reader as SqlDataReader, "Id");
            scoreLine.Province = (string)ToModelValue(reader as SqlDataReader, "Province");
            scoreLine.Year = (short)ToModelValue(reader as SqlDataReader, "Year");
            scoreLine.Major = (string)ToModelValue(reader as SqlDataReader, "Major");
            scoreLine.Category = (string)ToModelValue(reader as SqlDataReader, "Category");
            scoreLine.TopScore = (short)ToModelValue(reader as SqlDataReader, "TopScore");
            scoreLine.BottomScore = (short)ToModelValue(reader as SqlDataReader, "BottomScore");
            scoreLine.AvgScore = (short)ToModelValue(reader as SqlDataReader, "AvgScore");
            scoreLine.AdmitCount = (short)ToModelValue(reader as SqlDataReader, "AdmitCount");
            scoreLine.DelFlag = (bool)ToModelValue(reader as SqlDataReader, "DelFlag");
            scoreLine.AddTime = (DateTime)ToModelValue(reader as SqlDataReader, "AddTime");
            return scoreLine;
        }
        #endregion

        #region 18.C#值转数据库值空值处理
        /// <summary>
        /// C#值转数据库值空值处理
        /// </summary>
        /// <param name="value">C#值</param>
        /// <returns>处理后的数据库值</returns>
        public object ToDBValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
        #endregion

        #region 19.数据库值转C#值空值处理
        /// <summary>
        /// 数据库值转C#值空值处理
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <returns>C#值</returns>
        public object ToModelValue(IDataReader reader, string columnName)
        {
            if (reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                return null;
            }
            else
            {
                return reader[columnName];
            }
        }
        #endregion
    }
}
